
import xlrd3 as xlrd #xlrd
import pathlib
import os
import sys
import sqlite3
import configparser
import easygui
from inc import * 
#pip install xlrd xlwt
try:
    lock=conf.get("Lock","lock")
except:
    lock=False
finally:
    if(lock=="true"):
        a=input("数据已被锁定")
        sys.exit(0)
try:
    file=root/pathlib.Path("学生信息.xls")
except:
    easygui.exceptionbox("文件 <学生信息.xls> 读取错误")
db=sqlite3.connect(str(root/"yj.db"))
cur = db.cursor()
njn={'初中一年级':7,'初中二年级':8,'初中三年级':9,'小学六年级':6,'小学五年级':5,'小学四年级':4,'小学三年级':3,'小学二年级':2,'小学一年级':1}

sql="SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
r=cur.execute(sql)
#print(r.fetchall())
if(len(r.fetchall())==0):
    sql=r'DROP TABLE IF EXISTS "main"; '
    cur.execute(sql)
    sql=r'CREATE TABLE "main" ("ID" text NOT NULL, "kh" TEXT, "name" TEXT, "bj" TEXT, "nj" TEXT, "kc" TEXT, "z" integer, "p" integer, "i" integer, PRIMARY KEY ("ID") );'
    cur.execute(sql)
    sql=r'CREATE UNIQUE INDEX "kh" ON "main" ("kh" ASC );'
    cur.execute(sql)
    sql=r'CREATE  VIEW re AS SELECT	* FROM	main a JOIN ( SELECT kc, z, p FROM main GROUP BY kc, z, p HAVING count( * ) > 1 ) b ON a.kc = b.kc 	AND a.z = b.z 	AND a.p = b.p ORDER BY a.kc,a.z,a.p'
    cur.execute(sql)
    '''
    sql=r'CREATE UNIQUE INDEX "main"."unique" ON "main" (   "kc",   "z",   "p" );' 
    cur.execute(sql)     
    '''
    db.commit()
else:
    cur.execute("delete from main")
    db.commit()

print(str(file))
wb = xlrd.open_workbook(str(file))
wb_active=wb.sheets()[0]
wb_row=wb_active.nrows
print(wb_row)
cur.execute("delete from main")
for i in range(1,wb_row):
    #print(wb_active.cell_value(i,5),"\t",wb_active.cell_value(i,4),wb_active.cell_value(i,13),str(njn[wb_active.cell_value(i,2)])+"_"+str(wb_active.cell_value(i,3)),wb_active.cell_value(i,8))

    print(wb_active.cell_value(i,2))
    print(i)
    nj="{nj}".format(nj=int(wb_active.cell_value(i,4) if wb_active.cell_value(i,4) in (4,5,6,7,8,9) else njn[wb_active.cell_value(i,4)]))
    bj=getClassNo(wb_active.cell_value(i,3))
    sql=r'INSERT INTO main(ID, kh, name, bj,nj, kc, z, p,i) VALUES ("{id}", "{kh}","{name}", "{bj}",{nj} ,0,0,0,{i})'.format(
                id="%d"%(int((wb_active.cell_value(i,0)))),
                kh="%d"%(int(wb_active.cell_value(i,1))),
                name=wb_active.cell_value(i,2),
                nj=nj,
                bj=f"{nj}_{bj}",
                
                i=i
                

            )
    print(sql)
    
    try:
        cur.execute(sql)
    except:
        easygui.exceptionbox("数据异常")
        print("#################数据异常#########################")
        sys.exit()


db.commit()
easygui.msgbox("学生信息导入完成！")
